package com.css.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.css.entity.DataHouseQita;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author Dbj
 * @date 2024年09月19日  14:44
 */
@Mapper
public interface DataHouseQitaMapper extends BaseMapper<DataHouseQita> {
    @Select("select if(sum(house)*100 is null,0,sum(house)*100) house from data_houseqita where ST_Intersects(geom, st_geometryfromtext(#{geom},#{code}))")
    List<DataHouseQita> getByGeomBak(@Param("geom")String intersectGeom, @Param("code")String code);
    @Select("WITH sjz AS ( SELECT ST_GeomFromText ( #{geom},#{code} ) AS geom ) SELECT if(SUM( ( P ).val )*100 is null,0,SUM( ( P ).val )*100) AS house " +
            " FROM data_houseqita_raster r JOIN sjz s ON ST_Intersects ( r.geom, s.geom ) CROSS JOIN LATERAL ST_PixelAsPolygons ( ST_Clip ( r.geom, s.geom ) ) AS P " +
            " WHERE ( P ).val >= 0")
    List<DataHouseQita> getByGeom(@Param("geom")String intersectGeom, @Param("code")String code);
}
